package uk.ac.cam.juliet.zedcat.db;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class dbAnalysisDatabase extends JulietDbInterface{

	public dbAnalysisDatabase(String address) throws ClassNotFoundException, SQLException {
		super(address, "db.analysis", "ceasefire");
	}
	public dbAnalysisDatabase(String address, String username, String password) throws ClassNotFoundException, SQLException {
		super(address, username, password);
	}
	public List<Account> getAccounts() throws SQLException
	{
		/*
		 * The getAccounts method returns a LinkedList of Account classes which have
		 * been initialised based on the data held in tblAccount.
		 */
		
		// Select all of the account data from the account table.
		String accountSelectStmt = "SELECT AccountID, MediaID, Username, Password FROM tblAccount";
		PreparedStatement accountSelectQuery = databaseConnection.prepareStatement(accountSelectStmt);
		ResultSet accountIDs = accountSelectQuery.executeQuery();
		List<Account> accountList = new LinkedList<Account>();
		
		//For each retrieved AccountID, initialise the account object and add it to the list.
		while(accountIDs.next()) {
			
			/* 
			 * - SUSPENDED - None-urgent, so development suspended for now
			 * TODO - Tom Alexander.
			 * Fix the account initialisation on the following line such that the 5th
			 * argument is a LinkedList of corresponding EmailRecord classes. (Currently null).
			 */
			
			Account currentAccount = new Account(accountIDs.getString(1),accountIDs.getString(2),accountIDs.getString(3),accountIDs.getString(4), null);
			accountList.add(currentAccount);
		}
		
		accountIDs.close();
		accountSelectQuery.close();
		return accountList;
	}
	public List<Action> getActions() throws SQLException
	{
		//First, get a list of all of the ActionIDs
		String actionSelectStmt = "SELECT ActionID FROM tblAction";
		PreparedStatement actionSelectQuery = databaseConnection.prepareStatement(actionSelectStmt);
		ResultSet actionIDs = actionSelectQuery.executeQuery();

		String currActionID = null;
		List<Action> actionList = new LinkedList<Action>();

		//For each retrieved ActionID, build up the action object and add it to the list
		while(actionIDs.next())
		{
			currActionID = actionIDs.getString(1);
			actionList.add(getAction(currActionID));
		}
		
		actionIDs.close();
		actionSelectQuery.close();
		
		return actionList;
	}
	public Action getAction(String actionID) throws SQLException
	{
		String actionSelectStmt = "SELECT ActionID FROM tblAction";
		PreparedStatement actionSelectQuery = databaseConnection.prepareStatement(actionSelectStmt);
		ResultSet actionIDs = actionSelectQuery.executeQuery();
	
		//Check that this is a valid action ID
		if(!actionIDs.first()){
			return null;
		}
		
		actionIDs.close();
		actionSelectQuery.close();
	
		//Retrieve the tag list
		String tagSelectStmt = "SELECT TagID FROM tblTag WHERE ActionID = ?";
		PreparedStatement tagSelectQuery = databaseConnection.prepareStatement(tagSelectStmt);
		tagSelectQuery.setString(1, actionID);
		ResultSet tagIDs = tagSelectQuery.executeQuery();
	
		//Store it in a linked list
		List<String> tagIDList = new LinkedList<String>();
		while(tagIDs.next()){
			tagIDList.add(tagIDs.getString(1));
		}
	
		tagIDs.close();
		tagSelectQuery.close();
		
		//Now we need to build up some instances
		List<ActionInstance> instances = getActionInstances(actionID);
		
		//Create the Action object and return it
		Action newAction = new Action(actionID, tagIDList, instances);
		return newAction;
	}
	
	private String getEnterEventID() throws SQLException
	{
		String enterIDStmt = "SELECT EventID FROM tblEvent WHERE STRCMP(tblEvent.Desc, \"Enter\") = 0";
		PreparedStatement enterIDQuery = databaseConnection.prepareStatement(enterIDStmt);
		ResultSet enterIDResultSet = enterIDQuery.executeQuery();
		if(!enterIDResultSet.first())
		{
			System.err.println("Could not find an 'Enter' event... Check the contents of tblEvent");
			return null;
		}
		String enterID = enterIDResultSet.getString(1);
		enterIDResultSet.close();
		enterIDQuery.close();
		return enterID;
	}
	
	private String getExitEventID() throws SQLException
	{
		String exitIDStmt = "SELECT EventID FROM tblEvent WHERE STRCMP(tblEvent.Desc, \"Exit\") = 0";
		PreparedStatement exitIDQuery = databaseConnection.prepareStatement(exitIDStmt);
		ResultSet exitIDResultSet = exitIDQuery.executeQuery();
		if(!exitIDResultSet.first())
		{
			System.err.println("Could not find an 'Enter' event... Check the contents of tblEvent");
			return null;
		}
		String exitID = exitIDResultSet.getString(1);
		exitIDResultSet.close();
		exitIDQuery.close();
		return exitID;
	}
	
	private List<ActionInstance> getActionInstances(String actionID) throws SQLException
	{
		//Get the ID of the 'Enter' event
		String enterID = this.getEnterEventID();
	
		//Get the ID of the 'Exit' event
		String exitIDStmt = "SELECT EventID from tblEvent WHERE STRCMP(tblEvent.Desc, \"Exit\") = 0";
		PreparedStatement exitIDQuery = databaseConnection.prepareStatement(exitIDStmt);
		ResultSet exitIDResultSet = exitIDQuery.executeQuery();
		if(!exitIDResultSet.first())
		{
			System.err.println("Could not find an 'Exit' event... You're screwed");
		}
		String exitID = exitIDResultSet.getString(1);
		exitIDQuery.close();
	
		String actionSelectStmt = "SELECT ActionID FROM tblAction WHERE ActionID = " + actionID;
		PreparedStatement actionSelectQuery = databaseConnection.prepareStatement(actionSelectStmt);
		ResultSet actionIDs = actionSelectQuery.executeQuery();
	
		//Check that this is a valid action ID
		if(!actionIDs.first()){
			return null;
		}
		actionIDs.close();
		actionSelectQuery.close();
	
		//Select all of the sightings associated with the action
		String sightingSelectStmt = "SELECT SightingID, tblSighting.HotspotID, EventID, Time FROM tblSighting, tblHotspot, tblAction WHERE tblSighting.HotspotID = tblHotspot.HotspotID AND tblHotspot.ActionID = tblAction.ActionID AND STRCMP(tblAction.ActionID, ?) = 0 AND STRCMP(tblSighting.EventID, ?) = 0 ORDER BY Time";
		PreparedStatement sightingSelectQuery = databaseConnection.prepareStatement(sightingSelectStmt);
		sightingSelectQuery.setString(1, actionID);
		sightingSelectQuery.setString(2, enterID);
		ResultSet sightingResults = sightingSelectQuery.executeQuery();
	
		List<ActionInstance> instanceList = new LinkedList<ActionInstance>();
	
		//Now we must build the list of actionInstances
		while(sightingResults.next())
		{
			//Get the corresponding exit sighting
			Timestamp currentSightingTime = sightingResults.getTimestamp(4);
			Timestamp currentSightingExitTime = null;
			String exitSightingSelectStmt = "SELECT HotspotID, Time, SightingID FROM tblSighting WHERE EventID = ? AND (TIMEDIFF(Time, ?) >= 0) AND HotspotID = ? ORDER BY Time";
			PreparedStatement exitSelectQuery = databaseConnection.prepareStatement(exitSightingSelectStmt);
			exitSelectQuery.setString(1, exitID);
			exitSelectQuery.setTimestamp(2, sightingResults.getTimestamp(4));
			exitSelectQuery.setString(3, sightingResults.getString(2));
			ResultSet exitResult = exitSelectQuery.executeQuery();
	
			if(exitResult.first())
			{
				//If there is no exit record, either we missed it, or the cat's still there. Just disregard this sighting - either way, we don't know the duration
				currentSightingExitTime = exitResult.getTimestamp(2);
				ActionInstance newActionInstance = new ActionInstance(currentSightingTime, currentSightingExitTime.getTime() - currentSightingTime.getTime());
				instanceList.add(newActionInstance);
			}
			exitResult.close();
			exitSelectQuery.close();
		}
	
		sightingResults.close();
		sightingSelectQuery.close();
		return instanceList;
	}
	public String tagForID(String tagID) throws SQLException{
		String tagSelectStmt = "SELECT TagText FROM tblTag WHERE TagID = ?";
		PreparedStatement tagQuery = databaseConnection.prepareStatement(tagSelectStmt);
		tagQuery.setString(1, tagID);
		ResultSet tagRs = tagQuery.executeQuery();
		String tagText = null;
		if(tagRs.first()){
			tagText = tagRs.getString(1);
		}
		tagRs.close();
		tagQuery.close();
		return tagText;
	}
	public Action getMostRecentAction() throws SQLException
	{
		//Returns the most recent action
		//If no actions are found (i.e. we haven't seen the cat), then returns null
		String SightingSelectStmt = "SELECT SightingID, ActionID, Time FROM tblSighting, tblHotspot WHERE tblSighting.HotspotID = tblHotspot.HotspotID ORDER BY Time DESC";
		PreparedStatement sightingQuery = databaseConnection.prepareStatement(SightingSelectStmt);
		ResultSet sightingRs = sightingQuery.executeQuery();
		
		//Return null if there are no sightings
		if(!sightingRs.first()){
			return null;
		}
		
		Action currAction = getAction(sightingRs.getString(2));
		while((currAction.getInstances().size() <= 0) && sightingRs.next()){
			currAction = getAction(sightingRs.getString(2));
		}
		
		sightingRs.close();
		sightingQuery.close();
		
		if(currAction.getInstances().size() <= 0){
			System.out.println("Nothing with instances...");
			return null;
		}else{
			return currAction;
		}
	}
	public Action getMostRecentActionInstance() throws SQLException
	{
		//Will return an action object, with only one instance in its instances list: The most recent instance
		//Returns null if no ActionInstances found
		String SightingSelectStmt = "SELECT SightingID, ActionID, Time FROM tblSighting, tblHotspot WHERE tblSighting.HotspotID = tblHotspot.HotspotID ORDER BY Time DESC";
		PreparedStatement sightingQuery = databaseConnection.prepareStatement(SightingSelectStmt);
		ResultSet sightingRs = sightingQuery.executeQuery();
		
		if(!sightingRs.first()){
			return null;
		}
		
		String actionID = sightingRs.getString(2);
		sightingRs.close();
		sightingQuery.close();
		ActionInstance mostRecentActionInstance = getMostRecentActionInstance(actionID);
		while(mostRecentActionInstance == null && sightingRs.next()){
			mostRecentActionInstance = getMostRecentActionInstance(actionID);
		}
		if(mostRecentActionInstance == null){
			//No instances found - return!
			return null;
		}
		
		//Else, grab the tag data and construct the new Action
		String tagSelectStmt = "SELECT TagID FROM tblTag WHERE ActionID = ?";
		PreparedStatement tagSelectQuery = databaseConnection.prepareStatement(tagSelectStmt);
		tagSelectQuery.setString(1, actionID);
		ResultSet tagIDs = tagSelectQuery.executeQuery();
	
		//Store it in a linked list
		List<String> tagIDList = new LinkedList<String>();
		while(tagIDs.next()){
			tagIDList.add(tagIDs.getString(1));
		}
		
		tagIDs.close();
		tagSelectQuery.close();
		
		List<ActionInstance> instanceList = new LinkedList<ActionInstance>();
		instanceList.add(mostRecentActionInstance);
		
		Action mostRecent = new Action(actionID, tagIDList, instanceList);
		return mostRecent;
	}
	ActionInstance getMostRecentActionInstance(String actionID) throws SQLException
	{
		//Returns the most recent instance of an action
		String enterIDStmt = "SELECT EventID FROM tblEvent WHERE STRCMP(tblEvent.Desc, \"Enter\") = 0";
		PreparedStatement enterIDQuery = databaseConnection.prepareStatement(enterIDStmt);
		ResultSet enterIDResultSet = enterIDQuery.executeQuery();
		if(!enterIDResultSet.first())
		{
			System.err.println("Could not find an 'Enter' event... You're screwed");
		}
		String enterID = enterIDResultSet.getString(1);
		
		enterIDQuery.close();
	
		//Get the ID of the 'Exit' event
		String exitIDStmt = "SELECT EventID from tblEvent WHERE STRCMP(tblEvent.Desc, \"Exit\") = 0";
		PreparedStatement exitIDQuery = databaseConnection.prepareStatement(exitIDStmt);
		ResultSet exitIDResultSet = exitIDQuery.executeQuery();
		if(!exitIDResultSet.first())
		{
			System.err.println("Could not find an 'Exit' event... You're screwed");
		}
		String exitID = exitIDResultSet.getString(1);
		exitIDQuery.close();
	
		String actionSelectStmt = "SELECT ActionID FROM tblAction WHERE ActionID = " + actionID;
		PreparedStatement actionSelectQuery = databaseConnection.prepareStatement(actionSelectStmt);
		ResultSet actionIDs = actionSelectQuery.executeQuery();
	
		//Check that this is a valid action ID
		if(!actionIDs.first()){
			return null;
		}
		actionIDs.close();
		actionSelectQuery.close();
	
		//Select all of the sightings associated with the action
		String sightingSelectStmt = "SELECT SightingID, tblSighting.HotspotID, EventID, Time FROM tblSighting, tblHotspot, tblAction WHERE tblSighting.HotspotID = tblHotspot.HotspotID AND tblHotspot.ActionID = tblAction.ActionID AND STRCMP(tblAction.ActionID, ?) = 0 AND STRCMP(tblSighting.EventID, ?) = 0 ORDER BY Time DESC";
		PreparedStatement sightingSelectQuery = databaseConnection.prepareStatement(sightingSelectStmt);
		sightingSelectQuery.setString(1, actionID);
		sightingSelectQuery.setString(2, enterID);
		ResultSet sightingResults = sightingSelectQuery.executeQuery();
	
		ActionInstance instance = null;
	
		//Now we must build the list of actionInstances
		while(sightingResults.next() && instance == null)
		{
			//Get the corresponding exit sighting
			Timestamp currentSightingTime = sightingResults.getTimestamp(4);
			Timestamp currentSightingExitTime = null;
			String exitSightingSelectStmt = "SELECT HotspotID, Time, SightingID FROM tblSighting WHERE EventID = ? AND (TIMEDIFF(Time, ?) >= 0) AND HotspotID = ? ORDER BY Time";
			PreparedStatement exitSelectQuery = databaseConnection.prepareStatement(exitSightingSelectStmt);
			exitSelectQuery.setString(1, exitID);
			exitSelectQuery.setTimestamp(2, sightingResults.getTimestamp(4));
			exitSelectQuery.setString(3, sightingResults.getString(2));
			ResultSet exitResult = exitSelectQuery.executeQuery();
	
			if(exitResult.first())
			{
				//If there is no exit record, either we missed it, or the cat's still there. Just disregard this sighting - either way, we don't know the duration
				currentSightingExitTime = exitResult.getTimestamp(2);
				instance = new ActionInstance(currentSightingTime, currentSightingExitTime.getTime() - currentSightingTime.getTime());
			}
			exitResult.close();
			exitSelectQuery.close();
		}
	
		sightingResults.close();
		sightingSelectQuery.close();
		return instance;
	}
	public void clearStatTable() throws SQLException
	{
		String statClearStmt = "DELETE FROM tblStats";
		PreparedStatement statClearQuery = databaseConnection.prepareStatement(statClearStmt);
		statClearQuery.executeUpdate();
	}
	public List<Sighting> getSightingsSince(Timestamp sinceTime) throws SQLException {
		/*
		 * A method which queries the database and returns a LinkedList<Sighting> of all
		 * the sightings which occurred AFTER sinceTime.
		 */
		
		//Pull out the records from tblSighting
		String searchString = "SELECT SightingID, EventID, Time, SnapshotFilePath, ActionID, tblSighting.HotspotID FROM tblSighting, tblHotspot WHERE tblSighting.HotspotID = tblHotspot.HotspotID AND Time > ? ORDER BY Time";
		PreparedStatement sightingSearchStmt = databaseConnection.prepareStatement(searchString);
		sightingSearchStmt.setTimestamp(1, sinceTime);
		ResultSet sightingRs = sightingSearchStmt.executeQuery();
		
		
		
		
		//If there were no results, return null
		if(!sightingRs.first()){
			return null;
		}
		
		String enterId = this.getEnterEventID();				
		boolean enterBool = sightingRs.getString(2).equals(enterId); 
		Sighting currentSighting = new Sighting(sightingRs.getString(5), sightingRs.getInt(6), sightingRs.getString(1), enterBool, sightingRs.getTimestamp(3), sightingRs.getString(4));
		List<Sighting> sightingList = new LinkedList<Sighting>();
		sightingList.add(currentSighting);
		
		while(sightingRs.next())
		{
			enterBool = sightingRs.getString(2).equals(enterId);
			currentSighting = new Sighting(sightingRs.getString(5), sightingRs.getInt(6), sightingRs.getString(1), enterBool, sightingRs.getTimestamp(3), sightingRs.getString(4));
			sightingList.add(currentSighting);
		}
		sightingRs.close();
		sightingSearchStmt.close();
		return sightingList;
	}
	public String getSettingsEntryForKey(String key) throws SQLException
	{
		//Return the value associated with key in tblSettings
		String searchStmt = "SELECT Value FROM tblSettings WHERE STRCMP(tblSettings.Key, ?) = 0";
		PreparedStatement valueQuery = databaseConnection.prepareStatement(searchStmt);
		valueQuery.setString(1, key);
		ResultSet valueResult = valueQuery.executeQuery();
		
		if(!valueResult.first())
		{
			valueResult.close();
			valueQuery.close();
			return null;
		}
		String result = valueResult.getString(1);
		valueResult.close();
		valueQuery.close();
		return result;
	}
	public void setSettingsEntryForKey(String key, String value) throws SQLException
	{
		String keySearchStmt = "SELECT Value FROM tblSettings WHERE tblSettings.Key = ?";
		PreparedStatement keySearchQuery = databaseConnection.prepareStatement(keySearchStmt);
		keySearchQuery.setString(1, key);
		ResultSet keySearchResults = keySearchQuery.executeQuery();
		String updateStmt;
		if(!keySearchResults.first()){
			//Then the key didn't exist. We need to add it.
			updateStmt = "INSERT INTO tblSettings (tblSettings.Value, tblSettings.Key) VALUES (?, ?)";
		}else{
			updateStmt = "UPDATE tblSettings SET tblSettings.Value = ? WHERE tblSettings.Key = ?";
		}
		PreparedStatement insertQuery = databaseConnection.prepareStatement(updateStmt);
		insertQuery.setString(1, value);
		insertQuery.setString(2, key);
		insertQuery.execute();
		insertQuery.close();
		keySearchResults.close();
		keySearchQuery.close();
		databaseConnection.commit();
	}
	public void addStatsRecord(int hour, Date date, String actionID, int numOfMinutes) throws SQLException {
		//Will add a record to tblStats, or add the minutes to an existing record
		
		//First, set the hours, minutes, seconds and milliseconds to 0
		//Annoyingly, the appropriate methods for this have been deprecated, but here's a hack
		date.setTime(date.getTime() - (date.getTime() % 86400000));
		
		//Check to see if we're about to add a duplicate record
		String searchStmt = "SELECT Minutes FROM tblStats WHERE tblStats.Hour = ? AND tblStats.ActionID = ? AND tblStats.Date = ?";
		PreparedStatement searchQuery = databaseConnection.prepareStatement(searchStmt);
		searchQuery.setInt(1, hour);
		searchQuery.setString(2, actionID);
		searchQuery.setDate(3, date);
		ResultSet searchResults = searchQuery.executeQuery();
		String updateStmt;
		
		if(searchResults.first())
		{
			//Then there was already a record with the joint key - add the minutes to it
			updateStmt = "UPDATE tblStats SET tblStats.Minutes = tblStats.Minutes + ? WHERE tblStats.Hour = ? AND tblStats.ActionID = ? AND tblStats.Date = ?";
		}else{
			updateStmt = "INSERT INTO tblStats (tblStats.Minutes, tblStats.Hour, tblStats.ActionID, tblStats.Date) VALUES (?, ?, ?, ?)";
		}
		
		PreparedStatement updateQuery = databaseConnection.prepareStatement(updateStmt);
		
		searchResults.close();
		searchQuery.close();
		updateQuery.setInt(1, numOfMinutes);
		updateQuery.setInt(2, hour);
		updateQuery.setString(3, actionID);
		updateQuery.setDate(4, date);
		updateQuery.execute();
		updateQuery.close();
		databaseConnection.commit();
	}
	
	public void setLastProcessed(String string) {
		try {
			setSettingsEntryForKey("Processed",string);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return;
	}
	public Timestamp getAssociatedEntryTime(Sighting si) throws SQLException {
		String enterEventID = this.getEnterEventID();
		String sightingSearchStmt = "SELECT SightingID, Time FROM tblSighting WHERE tblSighting.HotspotID = ? AND Time <= ? AND tblSighitng.EventID = ? ORDER BY Time DESC LIMIT 1";
		PreparedStatement sightingQuery = databaseConnection.prepareStatement(sightingSearchStmt);
		sightingQuery.setInt(1, si.getHotspotID());
		sightingQuery.setTimestamp(2, si.getTime());
		sightingQuery.setString(3, enterEventID);
		ResultSet sightingResult = sightingQuery.executeQuery();
		if(!sightingResult.first())
		{
			//Then there is no corresponding entry
			sightingResult.close();
			sightingQuery.close();
			return null;
		}
		
		//If we get here, then we found one (and only one, due to the LIMIT statement
		Timestamp enterTime = sightingResult.getTimestamp(2);
		sightingResult.close();
		sightingQuery.close();
		return enterTime;
	}
	public Timestamp getTimestamp(String sightingID) throws SQLException {
		String sightingSearchStmt = "SELECT Time FROM tblSighting WHERE tblSighting.SightingId = ?";
		PreparedStatement sightingQuery = databaseConnection.prepareStatement(sightingSearchStmt);
		sightingQuery.setString(1, sightingID);
		ResultSet sightingSearchResults = sightingQuery.executeQuery();
		
		if(!sightingSearchResults.first()){
			//Then the sightingID does not exist
			sightingSearchResults.close();
			sightingQuery.close();
			return null;
		}
		
		Timestamp time = sightingSearchResults.getTimestamp(1);
		sightingSearchResults.close();
		sightingQuery.close();
		return time;
	}
	public String getActionName(String actionID) throws SQLException
	{ 
		String searchStmt = "SELECT tblAction.Desc FROM tblAction WHERE tblAction.ActionID = ?";
		PreparedStatement actionQuery = databaseConnection.prepareStatement(searchStmt);
		actionQuery.setString(1, actionID);
		ResultSet actionResult = actionQuery.executeQuery();
		if(!actionResult.first()){
			// Then invalid action ID!
			actionResult.close();
			actionQuery.close();
			return null;
		}
		String name = actionResult.getString(1);
		actionResult.close();
		actionQuery.close();
		return name;
	}
	public List<String>getTags(String actionID, boolean enterEvent, boolean male) throws SQLException
	{
		String eventID;
		String searchStmt;
		searchStmt = "SELECT TagText FROM tblTag WHERE tblTag.ActionID LIKE ? AND tblTag.EventID LIKE ? AND tblTag.Male = ?";
		if(enterEvent){
			eventID = this.getEnterEventID();
		}
		else{
			eventID = this.getExitEventID();
		}
		
		PreparedStatement tagQuery = databaseConnection.prepareStatement(searchStmt);
		tagQuery.setString(1, actionID);
		tagQuery.setString(2, eventID);
		tagQuery.setBoolean(3, male);
		ResultSet tagTextResults = tagQuery.executeQuery();
		//Count the records
		int recordCount = 1;
		if(!tagTextResults.first()){
			System.out.println("No results");
			tagTextResults.close();
			tagQuery.close();
			return null;
		}
		while(tagTextResults.next())
		{
			recordCount ++;
		}
		tagTextResults.first();
		List<String>tagList = new ArrayList<String>(recordCount);
		tagList.add(tagTextResults.getString(1));
		while(tagTextResults.next()){
			tagList.add(tagTextResults.getString(1));
		}
		
		//Remember to clean up after..
		tagTextResults.close();
		tagQuery.close();
		return tagList;
	}
}